import sqlalchemy.types
from sqlalchemy import text
import pandas as pd

from factorPool.momentums import f_turnover_return
from factorPool.volatitlity import volatility_N, fama, rvi_volatility
from factorPool.fundamental_quant import sue, bp_compound, ep_compound
from factorPool.priceVolumn import skew
from data_resource.data_bases import engine, engine2
from data_resource.data_bases import (FactorEarningSuprise, FactorReturnWgt, FactorPeriodVolatility, FactorFama,
                                      FactorVolskew, FactorRVI, FactorPE, FactorPB)
from functools import partial
from utilities.utilities_func import write_to_db, get_session, bulk_insert_with_orm


def factorCalcu_f_return_wgt():
    """
        每日计算因子值-60日换手率加权累计收益率，并写入数据库
    """
    # 查询最新时间
    with get_session(engine) as session:
        _maxdate = session.query(FactorReturnWgt.trading).order_by(FactorReturnWgt.trading.desc()).first()

    _sql1 = """
    select a.trade_date, a.ticker, a.close, a.open, a.pct_chg, ((c.turnover_rate/100)*(a.pct_chg/100)+1) as wgt_chg
    from quant_research.market_daily_ts a
    left join quant_research.indicator_daily as c on a.ticker=c.code and a.trade_date=c.trade_date
    where a.pct_chg > -30 and a.pct_chg < 30
    order by a.trade_date;
    """

    raw = pd.read_sql(_sql1, engine)
    raw.dropna(inplace=True, axis=0, how='any')
    raw.rename(columns={'trade_date': 'trading', 'ticker': 'code'}, inplace=True)

    print("----- 60日换手率加权收益率因子计算开始 -----")
    raw['signal'] = raw.groupby('code', group_keys=False)['wgt_chg'].apply(lambda x: f_turnover_return(x, 60))
    raw.dropna(inplace=True, axis=0, how='any')
    raw.reset_index(inplace=True, drop=True)
    raw.drop(columns=['close', 'open', ], inplace=True)

    _r = raw[raw['trading'] > _maxdate[0]].copy()
    if _r.empty:
        return
    else:
        print("----- 60日换手率加权收益率因子计算完成，开始写入数据库 -----")
        write_to_db("factor_returnWgt", engine, FactorReturnWgt, _r)
        write_to_db("factor_returnWgt", engine2, FactorReturnWgt, _r)  # 写入远程数据库
    return


def factorCalcu_f_20dVolSkew():
    """

        每日计算因子值：20日成交量加权收益率偏度
    """
    sql1 = """
        select a.trade_date as trading, a.code, a.turnover_rate,
            b.open, b.close, b.vol, b.pct_chg
        from quant_research.indicator_daily a
        left join quant_research.market_daily_ts b
            on a.trade_date=b.trade_date and a.code=b.ticker
        order by a.trade_date
    """
    _raw = pd.read_sql(sql1, engine)

    # 查询最新时间
    with get_session(engine) as session:
        _maxdate = session.query(FactorReturnWgt.trading).order_by(FactorReturnWgt.trading.desc()).first()

    raw_volReturn = _raw.groupby('code', as_index=False)[
        ['trading', 'code', 'close', 'open', 'vol', 'pct_chg']].apply(
        lambda x: skew(x, 'vol_return', 20)).reset_index()

    raw_volReturn.drop(columns=['level_0', 'level_1'], inplace=True)
    raw_volReturn.dropna(inplace=True)
    raw_volReturn.reset_index(inplace=True, drop=True)
    raw_volReturn.drop(columns=['close', 'open', 'vol', 'pct_chg'], inplace=True)

    _r = raw_volReturn[raw_volReturn['trading'] > _maxdate[0]].copy()
    if _r.empty:
        return
    else:
        print("----- 20日成交量加权收益率偏度因子计算完成，开始写入数据库 -----")
        write_to_db("factor_volSkew", engine, FactorVolskew, _r)
    return


def f_volatility_n_std():
    """
        240日基础波动率因子计算
    """
    # 查询最新时间
    with get_session(engine) as session:
        _maxdate = session.query(FactorPeriodVolatility.trading).order_by(FactorPeriodVolatility.trading.desc()).first()

    sql1 = """
    select a.trade_date, a.ticker, a.close, a.open, a.pct_chg /100 as pct_chg1
    from quant_research.market_daily_ts a
    join quant_research.basic_info_stock b on a.ticker=b.ticker
    where b.status='L' and b.list_date<'2014-06-01' and a.trade_date >= '2015-01-01' and a.pct_chg > -30 and a.pct_chg < 30
    order by a.trade_date;
    """

    raw = pd.read_sql(sql1, engine)

    print("----- 240日波动率因子计算开始 -----")
    partial_func = partial(volatility_N, n=240)
    raw['signal'] = raw.groupby('ticker')['pct_chg1'].transform(partial_func)

    raw.reset_index(inplace=True, drop=True)
    raw.dropna(inplace=True, axis=0, how='any')
    raw.rename(columns={'trade_date': 'trading', 'ticker': 'code', 'pct_chg1': 'perf_percent'},
               inplace=True)
    raw.drop(columns=['close', 'open', 'perf_percent'], inplace=True)
    raw.reset_index(drop=True, inplace=True)

    _r = raw[raw['trading'] > _maxdate[0]].copy()

    if _r.empty:
        return
    else:
        print("----- 240日波动率因子计算完成，开始写入数据库 -----")
        write_to_db("factor_periodVolatility", engine, FactorPeriodVolatility, _r)
        write_to_db("factor_periodVolatility", engine2, FactorPeriodVolatility, _r)

    return


def f_earningSuprise():
    """每日计算业绩超预期因子"""

    print("-------- factor_earningSuprise 计算开始 ---------")
    _r = sue()

    _r.to_sql("factor_earningSuprise", engine, if_exists='replace', index=False)

    return


# fama三因子每日计算
def f_fama():
    """每日计算fama三因子"""
    with get_session(engine) as session:
        _maxdate = session.query(FactorFama.trade_date).order_by(FactorFama.trade_date.desc()).first()

    r = fama(_maxdate[0].strftime("%Y-%m-%d"))
    print("-------- factor_fama 计算完成，开始写入本地数据库 ---------")
    write_to_db("factor_fama", engine, FactorFama, r)
    return


# rvi波动率因子 -- rvi_up因子效果最好
def f_rvi_up():
    sql1 = """
        select ticker as code, trade_date as trading, open, high, close, low
        from quant_research.market_daily_ts 
        where market_daily_ts.trade_date>'2010-01-01' and open is not null and close is not null and high is not null and low is not null
        order by trade_date
    """
    # 查询最新时间
    with get_session(engine) as session:
        _maxdate = session.query(FactorRVI.trading).order_by(FactorRVI.trading.desc()).first()

    raw_rvi_up = pd.read_sql(sql1, engine)
    raw_rvi_up.dropna(inplace=True)
    # 计算因子值
    print("---- 因子计算 -----")
    raw_rvi_up = rvi_volatility(raw_rvi_up, n=20, code_col='code', optimization='asymmetric')

    # # 初次写入
    # bulk_insert_with_orm(raw_rvi_up, 'factor_rvi', engine)

    _r = raw_rvi_up[raw_rvi_up['trading'] > _maxdate[0]].copy()
    if _r.empty:
        return
    else:
        print("----- RVI波动率因子计算完成，开始写入数据库 -----")
        write_to_db("factor_rvi", engine, FactorRVI, _r)
    return


# 计算财务分析指标
def f_finance_indicator():
    _sql = """
        with bs_base as (
            select bs.ticker, bs.f_ann_date, bs.end_date, bs.fix_assets,
                bs.total_assets, bs.total_liab, bs.inventories,
                -- 计算财务分析指标
                bs.fix_assets / NULLIF(bs.total_assets, 0) as asset_ratio,
                bs.total_liab / NULLIF(bs.total_assets, 0) as liab_ratio,
                bs.total_assets - bs.total_liab as net_assets,
                (
                    (bs.total_assets - bs.total_liab) + lag(bs.total_assets - bs.total_liab, 1) over (
                        partition by bs.ticker
                        order by bs.end_date
                        )
                ) / 2.0 as avg_equity
            from (
                select *, row_number() over (
                    partition by ticker, end_date
                    order by update_flag desc, f_ann_date
                ) as rn
                from quant_research.financials_bs_lt
            ) bs
            where bs.rn=1
        ),
        bs_quarter as (
            select *,
            -- 获取去年同期 平均权益
            lag(avg_equity,4) over (
                partition by ticker
                order by end_date
            ) as prev_avg_equity
            from bs_base
        ),
        is_quarter AS NOT MATERIALIZED(
            select iq.ticker, iq.f_ann_date, iq.end_date, iq.total_revenue, iq.n_income_attr_p,
                -- 获取去年同期 净利润
                lag(iq.n_income_attr_p, 4) over (
                    partition by iq.ticker
                    order by iq.end_date
                    ) as prev_n_income_attr_p
            from (
                select *, row_number() over (
                    partition by ticker, end_date
                    order by update_flag desc, f_ann_date
                    ) as rn
                from quant_research."financials_incomeState_quarter") as iq
            where iq.rn=1
        ),
        -- 申万行业分类重复的取in_date最新的
        sw_contituent as (
            select l1_name, ts_code
            from (
                select *, row_number() over (PARTITION BY ts_code order by in_date DESC) AS rn
                from quant_research.sw_industry_constituent
            ) as ranked
            where rn=1
        )
        -- 查询结果
        select a.ticker as code, c.l1_name as industry_name, a.f_ann_date, a.end_date, a.asset_ratio, a.liab_ratio,
            a.total_assets / NULLIF(b.total_revenue, 0) as capital_density,
            (360/NULLIF((b.total_revenue / NULLIF(a.inventories, 0)),0)) as inventory_turnover_days,
            b.n_income_attr_p, 
            b.n_income_attr_p / NULLIF(a.avg_equity,0) as roe_q,
            -- 计算ROE同比增长
            case
                when b.prev_n_income_attr_p > 0 and a.prev_avg_equity > 0 and a.avg_equity>0
                then
                    (b.n_income_attr_p/a.avg_equity - b.prev_n_income_attr_p/a.prev_avg_equity)
            end as roe_yoy_growth
        from bs_quarter as a
        left join is_quarter as b
            on a.ticker=b.ticker and a.end_date=b.end_date
        left join sw_contituent as c
            on c.ts_code=a.ticker
        order by end_date;
    """
    _data = pd.read_sql(_sql, engine)
    dtype_financials = {
        'code': sqlalchemy.types.VARCHAR,
        'industry_name': sqlalchemy.types.VARCHAR,
        'f_ann_date': sqlalchemy.types.Date,
        'end_date': sqlalchemy.types.Date,
        'asset_ratio': sqlalchemy.types.FLOAT,
        'liab_ratio': sqlalchemy.types.FLOAT,
        'capital_density': sqlalchemy.types.FLOAT,
        'inventory_turnover_days': sqlalchemy.types.FLOAT,
        'n_income_attr_p': sqlalchemy.types.FLOAT,
        'roe_q': sqlalchemy.types.FLOAT,
        'roe_yoy_growth': sqlalchemy.types.FLOAT
    }

    print("---------- 开始写入财务分析指标数据 ----------")
    _data.to_sql("factor_finance_indicator", engine, if_exists='replace', index=False,
                 dtype=dtype_financials)

    with engine.connect() as conn:
        conn.execute(
            text(
                "CREATE INDEX IF NOT EXISTS idx_factor_finance_code_end_date ON quant_research.factor_finance_indicator (code, end_date, industry_name)"
            )
        )
        conn.commit()


# 估值因子计算
def f_valuation():
    with get_session(engine) as session:
        pe_maxdate = session.query(FactorPE.trade_date).order_by((FactorPE.trade_date.desc())).first()
        pb_maxdate = session.query(FactorPB.trade_date).order_by((FactorPB.trade_date.desc())).first()
    # 计算因子值
    ep_data = ep_compound()
    bp_data = bp_compound()

    ep_data = ep_data[ep_data['trade_date'] > pe_maxdate[0]]
    bp_data = bp_data[bp_data['trade_date'] > pb_maxdate[0]]

    write_to_db("factor_ep_compound", engine, FactorPE, ep_data)
    write_to_db("factor_bp_compound", engine, FactorPB, bp_data)

    return


if __name__ == "__main__":
    f_valuation()
